package com.scuec.groupfive.mapper;

import com.scuec.groupfive.vo.WeakPointVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 薄弱知识点排行榜Mapper
 */
@Mapper
public interface WeakStatisticsMapper {

    /**
     * 查询用户薄弱知识点排行榜
     * @param userId 用户ID
     * @param subjectId 科目ID
     * @return 薄弱知识点列表
     */
    @Select("SELECT " +
            "    q.question_id AS questionId, " +
            "    s.subject_name AS subjectName, " +
            "    q.content AS content, " +
            "    wq.wrong_count AS wrongCount, " +
            "    ch.chapter_name AS chapterName, " +
            "    GROUP_CONCAT(kp.point_name SEPARATOR ', ') AS knowledgePoints " +
            "FROM " +
            "    wrong_questions wq " +
            "JOIN " +
            "    questions q ON wq.question_id = q.question_id " +
            "JOIN " +
            "    subjects s ON q.subject_id = s.subject_id " +
            "LEFT JOIN " +
            "    chapters ch ON q.chapter_id = ch.chapter_id " +
            "LEFT JOIN " +
            "    question_knowledge qk ON q.question_id = qk.question_id " +
            "LEFT JOIN " +
            "    knowledge_points kp ON qk.point_id = kp.point_id AND kp.level = 1 " +
            "WHERE " +
            "    wq.user_id = #{userId} " +
            "    AND q.subject_id = #{subjectId} " +
            "    AND q.question_type IN ('single_choice', 'fill_in_the_blank') " +
            "GROUP BY " +
            "    q.question_id, s.subject_name, q.content, wq.wrong_count, ch.chapter_name " +
            "ORDER BY " +
            "    wq.wrong_count DESC " +
            "LIMIT 10")
    List<WeakPointVO> getWeakPointRanking(@Param("userId") Long userId, @Param("subjectId") Integer subjectId);
}